package com.friendar.webapp;

import java.sql.*;

/**
 * This is singleton which will handle all calls related to user information in
 * the database It will be called from different servlets to handle different
 * situations
 * 
 * @author abansal
 * 
 */
public class ProfileHandler {
	/**
	 * Private instance
	 */
	private static ProfileHandler me;

	/**
	 * Connection to the database
	 */
	private Connection conn;
	/**
	 * This is the protected constructor for the singleton
	 */

	private boolean connected;

	private ProfileHandler() {
		// TODO add db initialization code here
		connected = false;
		try {
			// The newInstance() call is a work around for some
			// broken Java implementations
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager
					.getConnection("jdbc:mysql://localhost/friendar?"
							+ "user=friendar&password=friendar");
			connected = true;
			System.out.println("Connection established");
		} catch (Exception ex) {
			// handle the error
		}
	}

	/**
	 * Retrieves and returns a reference to this singleton object
	 * 
	 * @return an instance of the ProfileHandler
	 */
	public static ProfileHandler getInstance() {
		if (me == null)
			me = new ProfileHandler();
		return me;
	}

	/**
	 * Updates the location of a user in the DB
	 * 
	 * @param userId
	 * @param lat
	 * @param longi
	 * @return boolean to indicate success or failure
	 */
	public boolean updateLocation(String userId, String lat, String longi) {
		Statement st = null;
		ResultSet rs = null;
		boolean retVal = false;
		String sqlUser = "select count(id) from location where user_id="
				+ userId;
		String updateLoc = "update location SET latitude=" + lat
				+ ", longitude=" + longi + ", time_update=sysdate() where user_id=" + userId;
		String insertLoc = "insert into location (user_id, latitude, longitude, time_update) values ("
				+ userId + "," + lat + "," + longi + ", sysdate())";

		if (connected) {
			try {
				st = conn.createStatement();
				rs = st.executeQuery(sqlUser);
				if (rs.first()) {
					if (rs.getInt(1) > 0){
						// a row already exists, use update statement
						System.out.println("Updating existing row");
						if( st.executeUpdate(updateLoc) > 0)
							retVal = true;
					} else {
						System.out.println("Inserting new row");
						if( st.executeUpdate(insertLoc) > 0)
							retVal = true;
					}
					// now that the location is updated
					// calculate the distances and update the database
					FriendFinder ff = new FriendFinder();
					ff.calcDistToFriends(userId);
				}
			} catch (Exception e) {
				// do nothing
					
				System.out.println(updateLoc);
				System.out.println(insertLoc);
				e.printStackTrace();
			} finally {
				if (rs != null) {
					try {
						rs.close();
					} catch (SQLException sqlEx) { // ignore }

						rs = null;
					}
				}

				if (st != null) {
					try {
						st.close();
					} catch (SQLException sqlEx) { // ignore }

						st = null;
					}
				}

			}

		}
		return retVal;
		
	}

	/**
	 * Set the preference specified in pref to value specified in value
	 * 
	 * @param pref
	 * @param value
	 * @return boolean to indicate success or failure
	 */
	public boolean updatePreference(String pref, String value) {
		Statement st = null;
		ResultSet rs = null;
		boolean retVal = false;
		if (connected) {
			try {
				st = conn.createStatement();
				// rs = st.executeQuery(sql);
				if (rs.first()) {
					if (rs.getInt(1) > 0)
						retVal = true;
				}
			} catch (Exception e) {
				// do nothing

				System.out.println("error in executing select");
				e.printStackTrace();
			} finally {
				if (rs != null) {
					try {
						rs.close();
					} catch (SQLException sqlEx) { // ignore }

						rs = null;
					}
				}

				if (st != null) {
					try {
						st.close();
					} catch (SQLException sqlEx) { // ignore }

						st = null;
					}
				}

			}

		}
		return retVal;
		
	}

	/**
	 * Checks if connection to database has been made
	 * 
	 * @return true if connected
	 */
	public boolean isConnected() {
		return connected;
	}

	/**
	 * Checks if the user Id is a valid one in the database
	 * 
	 * @param id
	 *            to check in the database
	 * @return true or false
	 */
	public boolean isValidUser(String id) {
		boolean retVal = executeQuery("select count(id) from users where id=" + id);
		return retVal;
	}

	/**
	 * @param sql
	 * @return
	 */
	private boolean executeQuery(String sql) {
		Statement st = null;
		ResultSet rs = null;
		boolean retVal = false;
		if (connected) {
			try {
				st = conn.createStatement();
				rs = st.executeQuery(sql);
				if (rs.first()) {
					if (rs.getInt(1) > 0)
						retVal = true;
					System.out.println("No of users:" + rs.getInt(1));
				}
			} catch (Exception e) {
				// do nothing

				System.out.println("error in executing select");
				e.printStackTrace();
			} finally {
				if (rs != null) {
					try {
						rs.close();
					} catch (SQLException sqlEx) { // ignore }

						rs = null;
					}
				}

				if (st != null) {
					try {
						st.close();
					} catch (SQLException sqlEx) { // ignore }

						st = null;
					}
				}

			}

		}
		return retVal;
	}
	/**
	 * Adds a user into the database
	 * 
	 * @param name
	 * @param phonenum
	 * @param password
	 * @return
	 */
	public int addUser(String name, String phonenum, String password) {
		Statement st = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		
		
		int retVal = 0;
		
		if (connected) {
			try {
				st = conn.createStatement();
				pst = conn.prepareStatement("insert into users (name, phone_num, password) values (?,?,?)");
				pst.setString(1, name);
				pst.setString(2, phonenum);
				pst.setString(3, password);
				int ret = pst.executeUpdate();
				if(ret >0) {
					rs = st.executeQuery("select id from users where name='"+name + "' AND phone_num='" +
							phonenum + "' AND password='"+password+"'");
					if (rs.first()) {
						
							retVal = rs.getInt(1);
					}
					if(getRSSize(rs) > 1) {
						System.out.println("Multiple rows found");
						// TODO add check for multiple rows in the database
					}
					
				}
			} catch (Exception e) {
				// do nothing

				System.out.println("error in inserting user");
				//System.out.println(insertSQL);
				e.printStackTrace();
			} finally {
				if (rs != null) {
					try {
						rs.close();
					} catch (SQLException sqlEx) { // ignore }

						rs = null;
					}
				}

				if (st != null) {
					try {
						st.close();
					} catch (SQLException sqlEx) { // ignore }

						st = null;
					}
				}

			}

		}
		
		return retVal;
	}

	/**
	 * Calculates the size of the resultset. It positions the resultset at the first row
	 * after it is done;
	 * @param rs
	 * @throws SQLException
	 */
	private int getRSSize(ResultSet rs) throws SQLException {
		rs.last();
		int size=rs.getRow();
		if(size > 0)
			rs.first();
		
		return size;
	}
	/**
	 * Adds a friend relationship between existing users in the database
	 * 
	 * @param userId
	 * @param friendId
	 * @return
	 */
	public boolean addFriend(int userId, int friendId) {
		Statement st = null;
		ResultSet rs = null;
		boolean retVal = false;
		if (connected) {
			try {
				st = conn.createStatement();
				// check if this relationship already exists
				rs = st.executeQuery("Select * from friends where friend_1 = " +
						userId + " OR friend_2=" + userId);
				boolean exists = false;
				if(getRSSize(rs) > 0) {
					
					// check if the relationship exists
					
					do {
						int f1 = rs.getInt("friend_1");
						int f2 = rs.getInt("friend_2");
						if( (f1==userId && f2==friendId)|| (f2==userId && f1==friendId)) {
							// friendship exists
							exists = true;
						}
					} while (rs.next());
				}
				if(!exists) {
					// insert it
					rs.close();
					int ret = st.executeUpdate("insert into friends (friend_1, friend_2) values (" +
							userId + ", " + friendId + ")");
					if(ret > 0)
						retVal = true;
				}
				
			} catch (Exception e) {
				// do nothing

				System.out.println("error in adding friends");
				e.printStackTrace();
			} finally {
				if (rs != null) {
					try {
						rs.close();
					} catch (SQLException sqlEx) { // ignore }

						rs = null;
					}
				}

				if (st != null) {
					try {
						st.close();
					} catch (SQLException sqlEx) { // ignore }

						st = null;
					}
				}

			}
		}
		return retVal;
	}
}
